{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d8380914-0c2b-4c17-8a1f-27c2db4db907",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_cd</th>\n",
       "      <th>stock_nm</th>\n",
       "      <th>beg_dt</th>\n",
       "      <th>area</th>\n",
       "      <th>sjkzr</th>\n",
       "      <th>industry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000410</td>\n",
       "      <td>*ST沈机</td>\n",
       "      <td>19960718</td>\n",
       "      <td>辽宁</td>\n",
       "      <td>国务院国有资产监督管理委员会</td>\n",
       "      <td>机床制造</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000638</td>\n",
       "      <td>*ST万方</td>\n",
       "      <td>19961126</td>\n",
       "      <td>吉林</td>\n",
       "      <td>白山市江源区财政局</td>\n",
       "      <td>软件服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000809</td>\n",
       "      <td>ST新城</td>\n",
       "      <td>19980616</td>\n",
       "      <td>辽宁</td>\n",
       "      <td>铁岭市财政局</td>\n",
       "      <td>区域地产</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000972</td>\n",
       "      <td>ST中基</td>\n",
       "      <td>20000926</td>\n",
       "      <td>新疆</td>\n",
       "      <td>新疆生产建设兵团第六师国有资产监督管理委员会</td>\n",
       "      <td>食品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>002021</td>\n",
       "      <td>ST中捷</td>\n",
       "      <td>20040715</td>\n",
       "      <td>浙江</td>\n",
       "      <td>玉环市财政局</td>\n",
       "      <td>纺织机械</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>002069</td>\n",
       "      <td>ST獐子岛</td>\n",
       "      <td>20060928</td>\n",
       "      <td>辽宁</td>\n",
       "      <td>大连市人民政府国有资产监督管理委员会</td>\n",
       "      <td>渔业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>002102</td>\n",
       "      <td>ST冠福</td>\n",
       "      <td>20061229</td>\n",
       "      <td>福建</td>\n",
       "      <td>荆州市人民政府国有资产监督管理委员会</td>\n",
       "      <td>化学制药</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>002200</td>\n",
       "      <td>ST交投</td>\n",
       "      <td>20071221</td>\n",
       "      <td>云南</td>\n",
       "      <td>云南省国有资产监督管理委员会</td>\n",
       "      <td>建筑工程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>002313</td>\n",
       "      <td>*ST日海</td>\n",
       "      <td>20091203</td>\n",
       "      <td>深圳</td>\n",
       "      <td>珠海市人民政府国有资产监督管理委员会</td>\n",
       "      <td>通信设备</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>002470</td>\n",
       "      <td>ST金正</td>\n",
       "      <td>20100908</td>\n",
       "      <td>山东</td>\n",
       "      <td>临沭县国有资产管理服务中心</td>\n",
       "      <td>农药化肥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>002586</td>\n",
       "      <td>*ST围海</td>\n",
       "      <td>20110602</td>\n",
       "      <td>浙江</td>\n",
       "      <td>余姚市国有资产管理办公室</td>\n",
       "      <td>建筑工程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>002692</td>\n",
       "      <td>ST远程</td>\n",
       "      <td>20120808</td>\n",
       "      <td>江苏</td>\n",
       "      <td>无锡市人民政府国有资产监督管理委员会</td>\n",
       "      <td>电气设备</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>002781</td>\n",
       "      <td>*ST奇信</td>\n",
       "      <td>20151222</td>\n",
       "      <td>江西</td>\n",
       "      <td>新余市国有资产监督管理委员会</td>\n",
       "      <td>装修装饰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>300256</td>\n",
       "      <td>*ST星星</td>\n",
       "      <td>20110819</td>\n",
       "      <td>江西</td>\n",
       "      <td>萍乡经济技术开发区管理委员会</td>\n",
       "      <td>元器件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>300330</td>\n",
       "      <td>*ST计通</td>\n",
       "      <td>20120619</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市国有资产监督管理委员会</td>\n",
       "      <td>软件服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>600136</td>\n",
       "      <td>ST明诚</td>\n",
       "      <td>19980303</td>\n",
       "      <td>湖北</td>\n",
       "      <td>武汉市人民政府国有资产监督管理委员会</td>\n",
       "      <td>影视音像</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>600239</td>\n",
       "      <td>ST云城</td>\n",
       "      <td>19991202</td>\n",
       "      <td>云南</td>\n",
       "      <td>云南省人民政府国有资产监督管理委员会</td>\n",
       "      <td>区域地产</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>600241</td>\n",
       "      <td>ST时万</td>\n",
       "      <td>20001128</td>\n",
       "      <td>辽宁</td>\n",
       "      <td>辽宁省人民政府国有资产监督管理委员会</td>\n",
       "      <td>电气设备</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>600387</td>\n",
       "      <td>ST海越</td>\n",
       "      <td>20040218</td>\n",
       "      <td>浙江</td>\n",
       "      <td>铜川市人民政府国有资产监督管理委员会</td>\n",
       "      <td>石油贸易</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>600388</td>\n",
       "      <td>ST龙净</td>\n",
       "      <td>20001229</td>\n",
       "      <td>福建</td>\n",
       "      <td>闽西兴杭国有资产投资经营有限公司</td>\n",
       "      <td>环境保护</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>600515</td>\n",
       "      <td>ST基础</td>\n",
       "      <td>20020806</td>\n",
       "      <td>海南</td>\n",
       "      <td>海南省国有资产监督管理委员会</td>\n",
       "      <td>机场</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>600601</td>\n",
       "      <td>*ST方科</td>\n",
       "      <td>19901219</td>\n",
       "      <td>上海</td>\n",
       "      <td>中华人民共和国教育部</td>\n",
       "      <td>元器件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>600608</td>\n",
       "      <td>ST沪科</td>\n",
       "      <td>19920327</td>\n",
       "      <td>上海</td>\n",
       "      <td>昆明市人民政府国有资产监督管理委员会</td>\n",
       "      <td>商贸代理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>600719</td>\n",
       "      <td>ST热电</td>\n",
       "      <td>19960716</td>\n",
       "      <td>辽宁</td>\n",
       "      <td>大连市人民政府国有资产监督管理委员会</td>\n",
       "      <td>供气供热</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>600726</td>\n",
       "      <td>*ST华源</td>\n",
       "      <td>19960701</td>\n",
       "      <td>黑龙江</td>\n",
       "      <td>中国华电集团有限公司</td>\n",
       "      <td>火力发电</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>600734</td>\n",
       "      <td>ST实达</td>\n",
       "      <td>19960808</td>\n",
       "      <td>福建</td>\n",
       "      <td>福建省人民政府国有资产监督管理委员会</td>\n",
       "      <td>IT设备</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stock_cd stock_nm    beg_dt area                   sjkzr industry\n",
       "0    000410    *ST沈机  19960718   辽宁          国务院国有资产监督管理委员会     机床制造\n",
       "1    000638    *ST万方  19961126   吉林               白山市江源区财政局     软件服务\n",
       "2    000809     ST新城  19980616   辽宁                  铁岭市财政局     区域地产\n",
       "3    000972     ST中基  20000926   新疆  新疆生产建设兵团第六师国有资产监督管理委员会       食品\n",
       "4    002021     ST中捷  20040715   浙江                  玉环市财政局     纺织机械\n",
       "5    002069    ST獐子岛  20060928   辽宁      大连市人民政府国有资产监督管理委员会       渔业\n",
       "6    002102     ST冠福  20061229   福建      荆州市人民政府国有资产监督管理委员会     化学制药\n",
       "7    002200     ST交投  20071221   云南          云南省国有资产监督管理委员会     建筑工程\n",
       "8    002313    *ST日海  20091203   深圳      珠海市人民政府国有资产监督管理委员会     通信设备\n",
       "9    002470     ST金正  20100908   山东           临沭县国有资产管理服务中心     农药化肥\n",
       "10   002586    *ST围海  20110602   浙江            余姚市国有资产管理办公室     建筑工程\n",
       "11   002692     ST远程  20120808   江苏      无锡市人民政府国有资产监督管理委员会     电气设备\n",
       "12   002781    *ST奇信  20151222   江西          新余市国有资产监督管理委员会     装修装饰\n",
       "13   300256    *ST星星  20110819   江西          萍乡经济技术开发区管理委员会      元器件\n",
       "14   300330    *ST计通  20120619   上海          上海市国有资产监督管理委员会     软件服务\n",
       "15   600136     ST明诚  19980303   湖北      武汉市人民政府国有资产监督管理委员会     影视音像\n",
       "16   600239     ST云城  19991202   云南      云南省人民政府国有资产监督管理委员会     区域地产\n",
       "17   600241     ST时万  20001128   辽宁      辽宁省人民政府国有资产监督管理委员会     电气设备\n",
       "18   600387     ST海越  20040218   浙江      铜川市人民政府国有资产监督管理委员会     石油贸易\n",
       "19   600388     ST龙净  20001229   福建        闽西兴杭国有资产投资经营有限公司     环境保护\n",
       "20   600515     ST基础  20020806   海南          海南省国有资产监督管理委员会       机场\n",
       "21   600601    *ST方科  19901219   上海              中华人民共和国教育部      元器件\n",
       "22   600608     ST沪科  19920327   上海      昆明市人民政府国有资产监督管理委员会     商贸代理\n",
       "23   600719     ST热电  19960716   辽宁      大连市人民政府国有资产监督管理委员会     供气供热\n",
       "24   600726    *ST华源  19960701  黑龙江              中国华电集团有限公司     火力发电\n",
       "25   600734     ST实达  19960808   福建      福建省人民政府国有资产监督管理委员会     IT设备"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from comm.conn import Conn\n",
    "conn=Conn()\n",
    "sql=\"\"\"select DISTINCT s.stock_cd,s.stock_nm,s.beg_dt,s.area,i.sjkzr,s.industry from stocks s\n",
    "left join stock_info i on s.stock_cd=i.stock_cd\n",
    "where s.stock_nm like '%ST%' and (i.sjkzr like '%财政局%' or i.sjkzr like '%国有%' or i.sjkzr like '中国%' or i.sjkzr like '%委员会%'  or i.sjkzr like '%共和国%')\"\"\"\n",
    "gy_st=conn.select(sql)\n",
    "gy_st"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d78b928-2ead-408f-bcda-6d33d61f64a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"\"\"with a as(\n",
    "select stock_cd,max(rpt_dt) rpt_dt from top10_holder group by stock_cd),\n",
    "b as(select h.stock_cd,max(hold_vol) hold_vol,max(a.rpt_dt) rpt_dt  --,rank() over(PARTITION BY  h.stock_cd ORDER BY h.hold_vol) as rk  \n",
    "from top10_holder h INNER JOIN a on h.stock_cd=a.stock_cd and a.rpt_dt=h.rpt_dt\n",
    "group by h.stock_cd),\n",
    "rs as(\n",
    "select h.* from top10_holder h \n",
    "--INNER JOIN a on h.stock_cd=a.stock_cd and a.rpt_dt=h.rpt_dt\n",
    "INNER JOIN b on h.stock_cd=b.stock_cd and b.hold_vol=h.hold_vol and h.rpt_dt=b.rpt_dt),\n",
    "p as(SELECT ts_code,close from stock_trade \n",
    "where trade_date in(\n",
    "select max(trade_date) from stock_trade)),\n",
    "fi as(SELECT SECURITY_CODE stock_cd,EPSKCJB,BPS,MGZBGJ,TOTALOPERATEREVE,PARENTNETPROFIT,KCFJCXSYJLR,TOTALOPERATEREVETZ\n",
    "\t from stock_fina_kpi \n",
    "where REPORT_DATE in(\n",
    "select max(REPORT_DATE) from stock_fina_kpi)  )\n",
    "select DISTINCT s.stock_cd,s.stock_nm,--s.beg_dt,\n",
    "s.area,i.sjkzr,s.industry,\n",
    "rs.holder_nm,--round(rs.hold_vol/10000,2) hold_vol,\n",
    "rs.hold_rate,--rs.chng_typ,\n",
    "p.close price ,\n",
    "round(EPSKCJB,4) 扣非每股收益,round(BPS,2) 每股净资产,round(MGZBGJ,2) 每股资本公积,\n",
    "round(TOTALOPERATEREVE/100000000,2) 总营收,\n",
    "round(PARENTNETPROFIT/100000000,2)  净利润,\n",
    "round(KCFJCXSYJLR/100000000,2) 扣非净利润,\n",
    "round(TOTALOPERATEREVETZ,2) 营收同比增长\n",
    "from stocks s\n",
    "left join stock_info i on s.stock_cd=i.stock_cd\n",
    "LEFT JOIN rs on rs.stock_cd=s.stock_cd\n",
    "LEFT JOIN p on s.ts_code=p.ts_code\n",
    "left join fi on s.stock_cd=fi.stock_cd\n",
    "where s.stock_nm like '%ST%' and not(i.sjkzr like '%财政局%' or i.sjkzr like '%国有%' or\n",
    " i.sjkzr like '中国%' or i.sjkzr like '%委员会%'  or i.sjkzr like '%共和国%')\n",
    "and TOTALOPERATEREVE>50000000 and PARENTNETPROFIT>-10000000\n",
    "ORDER BY p.close\n",
    "\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "668746d5-49e8-4c5a-8bfd-29158afcf368",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2022-09-12 12:05:50,008] INFO  [tsapis.py:28] 从数据库中获取股票列表\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "828260e042fc45cf9512724eef249e6b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=4909.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "from comm.tsapis import Tsapi, logger\n",
    "# from tqdm import tqdm\n",
    "from tqdm.notebook import tqdm as tqdm\n",
    "import progressbar\n",
    "p = progressbar.ProgressBar()\n",
    "ts=Tsapi()\n",
    "    #if 'stocks' not in dir():\n",
    "stocks=ts.get_stock_list()\n",
    "        # 查询当前所有正常上市交易的股票列表\n",
    "i=0\n",
    "for js in tqdm(stocks.to_dict('records')):\n",
    "    i = i + 1\n",
    "    import time\n",
    "    time.sleep(0.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2f94028-a72f-4d4f-888b-450022ce6258",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
